Licensing Requirements for SQL Profiles
Jonathan Lewis reminded me on my last post that using SQL Profiles (because they are part of the SQL Tuning Advisor) requires a license for Oracle Tuning Pack (which requires a license for the Diagnostics Pack). He also mentioned that Baselines did not require any additional license (at least creating and using Baselines on SQL statements). It’s been a while since I worked on a database that didn’t have both packs, but frankly I wasn’t sure I had a good handle of what was allowed and what wasn’t. So I thought it might be worthwhile to check. There is an easy way to check by the way. I did a post a while back on Tuning Pack and Diagnostic Pack license requirements for running AWR and how to check what was allowed and what wasn’t using the CONTROL_MANAGEMENT_PACK_ACCESS parameter. Here’s a link to the post:
Here’s an example using the same technique to show that SQL Profiles are indeed disabled by turning off the Diagnostic and Tuning Packs (at least on 11.2.02).
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 7 21:15:21 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@SANDBOX1> @parms
Enter value for parameter: management_pack
Enter value for isset: Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
control_management_pack_access DIAGNOSTIC+TUNING TRUE TRUE TRUE
SYS@SANDBOX1> @flush_pool
System altered.
SYS@SANDBOX1> @avgskew
AVG(PK_COL)
-----------
16093748.8
1 row selected.
SYS@SANDBOX1> /
AVG(PK_COL)
-----------
16093748.8
1 row selected.
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
Plan hash value: 568322376
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44486 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 32M| 335M| 44486 (1)| 00:08:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1">0)
filter("COL1">0)
Note
-----
- SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
24 rows selected.
SYS@SANDBOX1> @flush_pool
System altered.
SYS@SANDBOX1> alter system set control_management_pack_access='none';
System altered.
SYS@SANDBOX1> @avgskew
AVG(PK_COL)
-----------
16093748.8
1 row selected.
SYS@SANDBOX1> /
AVG(PK_COL)
-----------
16093748.8
1 row selected.
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
Plan hash value: 568322376
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44486 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 32M| 335M| 44486 (1)| 00:08:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1">0)
filter("COL1">0)
20 rows selected.
SYS@SANDBOX1> alter system set control_management_pack_access='DIAGNOSTIC+TUNING';
System altered.
SYS@SANDBOX1> @flush_pool
System altered.
SYS@SANDBOX1> @avgskew
AVG(PK_COL)
-----------
16093748.8
1 row selected.
SYS@SANDBOX1> /
AVG(PK_COL)
-----------
16093748.8
1 row selected.
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
Plan hash value: 568322376
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44486 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 32M| 335M| 44486 (1)| 00:08:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1">0)
filter("COL1">0)
Note
-----
- SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
24 rows selected.
So as you can see, there was a SQL Profile on the statement that was used when Diagnostic and Tuning Packs were enabled, but when I set CONTROL_MANAGEMENT_PACK_ACCESS to NONE, the SQL Profile was ignored. Let’s try the same test with a Baseline.
SYS@SANDBOX1> @flush_pool
System altered.
SYS@SANDBOX1> @avgskew
AVG(PK_COL)
-----------
16093748.8
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: 05cq2hb1r37tr
Enter value for plan_hash_value: 568322376
Enter value for fixed (NO):
Enter value for enabled (YES):
Enter value for plan_name (SQLID_sqlid_planhashvalue):
sql_id: 05cq2hb1r37tr
plan_hash_value: 568322376
fixed: NO
enabled: YES
Baseline SQLID_05cq2hb1r37tr_568322376 created.
PL/SQL procedure successfully completed.
SYS@SANDBOX1> @avgskew
AVG(PK_COL)
-----------
16093748.8
SYS@SANDBOX1> /
AVG(PK_COL)
-----------
16093748.8
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
Plan hash value: 568322376
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44486 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 32M| 335M| 44486 (1)| 00:08:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1">0)
filter("COL1">0)
Note
-----
- SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
- SQL plan baseline SQLID_05CQ2HB1R37TR_568322376 used for this statement
25 rows selected.
SYS@SANDBOX1> -- so Baseline and SQL Profile Used
SYS@SANDBOX1>
SYS@SANDBOX1> alter system set control_management_pack_access='none';
System altered.
SYS@SANDBOX1> @flush_pool
System altered.
SYS@SANDBOX1> @avgskew
AVG(PK_COL)
-----------
16093748.8
SYS@SANDBOX1> /
AVG(PK_COL)
-----------
16093748.8
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
Plan hash value: 568322376
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44486 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 32M| 335M| 44486 (1)| 00:08:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1">0)
filter("COL1">0)
Note
-----
- SQL plan baseline SQLID_05CQ2HB1R37TR_568322376 used for this statement
24 rows selected.
So Baselines apparently do not require licenses for the Tuning and Diagnostics Packs, at least they aren’t disabled by setting the CONTROL_MANAGEMENT_PACK_ACCESS parameter to NONE.
Kerry,
Nice follow-up.
A further “corroborative” thought – “cardinality feedback” happens automatically, and works by creating SQL Baselines; so, whatever the intent of the licence document, your system may end up using SQL Baselines even when you (as a DBA) had never done anything with them.
Nit-picking apology:
Licence is a noun
License is a verb
I have to admit that I sometimes forget which is which, but then analogy is helpful:
device is a noun
devise is a verb
Ha, you Brits are so picky about grammar. In Texas most people can’t spell any version of licen(c/s)e, much less tell the difference between a noun and a verb (I’m proudly including myself in that group by the way). I learned something new today though. You are absolutely correct in your comments about the word “license”, but apparently somewhere along the way, whoever was in charge of grammar in America decided that having a word that was pronounced the same but spelled two different ways depending on whether it was a noun or a verb was Bullocks.
I was taught in grade school that there was only one word (license). I always figured licence was just a difference in spelling used by people speaking the Queens English (UK and Canada – maybe Australia and New Zealand). I never had any idea that you guys had two versions of the word to keep straight. After working on a project for a couple of months in London I had trouble with a couple of words that started wanting to have British spelling. I still struggle with that a little bit now and again and actually “license” is one of those words I struggle with. But I never had any idea you guys used both spellings.
Here’s a little excerpt from somewhere on the web citing differences in the language (so it must be true):
I once heard someone say that England and America were two countries separated by a common language. How true.
I come from a part of the country (U.S.A.) where people use the term license as though it’s plural. “The officer asked for my license, so I handed them over.” That’s what I call a licentious use of the language.
I’ve checked with the 11gR2 License guide, and here as well only
Automatic Plan Evolution of SQL Plan Management is listed as part of the tuning pack, so Manual Plan Evolution and Sql plan management itself seem to be part of the EE without additional option.
Thank you for pointing to this interesting detail.
Hi Kerry,
I really liked your response đŸ™‚ Very funny.
Today I was not in mood to work and then (as usual) I started reading technical matter and your name strike to me. Now after reading this post, I am in good mood.
Thanks!
Neeraj
[…] SQL profile It is useful with “force_matching” option for SQL with bad plan & NOT using bind variables. If you want to know it, read this blog. Be careful, profiles created by SQL Tuning Advisor may change plans when stats change. Also SQL profile requires a license. http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/ http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/ http://kerryosborne.oracle-guy.com/2011/01/licensing-requirements-for-sql-profiles/ […]
I red your intersting researches about tuning and licencing.
But, i observe somethings different.
First, on my 11.2.0.2, control_management_pack_access was set to DIAG+TUN.
I use one time the tuning advisor and implement PROFILE.
For licencing compliance, we reset control_management_pack_access to NONE (before the end of 2011)…
So, normaly, SQL Profile can’t be used because it’s a part of TUN.
But, when i check dba_usage i get :
select * from dba_feature_usage_statistics where currently_used = ‘TRUE’ and name = ‘SQL Profile’
I obtain :
3432156699 SQL Profile 11.2.0.2.0 5 15 TRUE 06/12/2011 23:46:55 03/01/2012 23:33:45 2 (HUGECLOB) 03/01/2012 23:33:45 602864 604800 SQL profiles have been used.
So SQL Profile was used when management_pack is NONE…
Last things, the column Feature_info give :
Total so far: 2, Manual: 2, Auto: 0, Enabled: 2, Category count: 1
So maybe there is a difference of licencing if you use it Manualy or automaticaly ?
On the documentation for licencing :
http://docs.oracle.com/cd/B28359_01/license.111/b28287/options.htm#CIHDDBCG
there is no reference about SQL Profile….
So…
Great
Fred
Hi Frederic,
The link is for 11.1 documentation. 11.2 does reference DBMS_SQLTUNE package which is what I have used to create a manual SQL Profile.
http://docs.oracle.com/cd/E11882_01/license.112/e10594.pdf
I’ll go back and have a look at the behavior again and see what I come up with.
Kerry
Hi,
The 12c documentation explicitely lists SQL Profiles in the features where licenses for Oracle Tuning Pack are required.
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC170
Regards,
Franck.
I was hoping you would share your script to create an sql basline.
SYS@SANDBOX1> @create_baseline
Thanks a lot
Hi George,
I have shared it. You can use the search box at the top of the blog to search for it and it will return a list of all the posts that have referenced it. Here’s a direct link for you so you don’t have to go looking:
create_baseline.sql
Kerry
[…] http://kerryosborne.oracle-guy.com/2011/01/licensing-requirements-for-sql-profiles/ […]
Kerry,
Thanks for the detailed analysis…
I have tested this in SE edition and it is not working for Standard Edition – the baselines…do you suggest creating an outline…?
It is not working for SE edition……
Hi Sanjay,
Sorry to resurrect this discussion after nearly 6 months, but when you say it’s not working for SE, what is it that you mean isn’t working?
The reason I ask is that we are using SE 12.1.0.1 for some of our databases, with control_management_pack_access set to none, but sql profiles still appear to be used. We’d like to try to replace these with baselines, so I’m looking for clarification on whether you mean baselines aren’t working, or disabling of sql profiles isn’t working.
Many thanks,
Rob
Outlines are a bit flaky (and officially deprecated for a long time) but yes they still do work. A SQL Profile or SQL Patch (if it is a small set of hints) might be a better option than Outlines – assuming they are viable in SE. But I’m not a licensing expert – so please verify for yourself.
Kerry